package edu.cs.usask.ca.silver.persistence.datasource;

import java.io.StringWriter;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.UUID;

import javax.sql.DataSource;
import javax.transaction.Status;
import javax.transaction.UserTransaction;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.apache.commons.codec.net.BCodec;
import org.apache.log4j.Logger;
import org.w3c.dom.Document;
import org.w3c.dom.Element;

import com.atomikos.icatch.jta.UserTransactionImp;

/**
 * This class contains methods that are available to all database adapters. It
 * kindly shares utility objects that are used by all database adapters; it is
 * also the place where the set of shared utility methods dwelled.
 * 
 * @author Yudi Xue
 * 
 */
public abstract class AbstractStorage {
	/**
	 * Instance of org.apache.commons.codec.net.BCodec for encoding any String
	 * that is supposed to be stored in the DB.
	 */
	protected static BCodec myBCodec = new BCodec();
	private static Logger logger = Logger.getLogger(AbstractStorage.class);

	/**
	 * Utility method to start a transaction and get a connection.
	 * 
	 * @param ds
	 *            connected XA datasource
	 * @return Connection The connection.
	 */
	protected Connection getConnection(DataSource ds) throws Exception {
		Connection conn = null;

		// Retrieve of construct the UserTransaction
		UserTransaction utx = new UserTransactionImp();
		utx.setTransactionTimeout(60);

		// First, create a transaction
		utx.begin();
		conn = ds.getConnection();

		return conn;
	}

	/**
	 * Utility method to close the connection and terminate the transaction.
	 * This method does all XA related tasks and should be called within a
	 * transaction. When it returns, the transaction will be terminated.
	 * 
	 * @param ds
	 *            connected XA datasource
	 * @param conn
	 *            The connection.
	 * @param error
	 *            Indicates if an error has occurred or not. If true, the
	 *            transaction will be rolled back. If false, the transaction
	 *            will be committed.
	 */
	protected void closeConnection(DataSource ds, Connection conn, boolean error)
			throws Exception {
		if (conn != null)
			conn.close();

		UserTransaction utx = new UserTransactionImp();
		if (utx.getStatus() != Status.STATUS_NO_TRANSACTION) {
			if (error)
				utx.rollback();
			else
				utx.commit();
		} else
			logger.info("WARNING: closeConnection called outside a tx");

	}

	/* -------- GENERAL DB METHODS - called by other methods -------------- */
	/**
	 * Method gets a connection from the connection pool and then executes the
	 * string passed in as parameter.
	 * 
	 * @param ds
	 *            connected XA datasource
	 * @param sqlStmt
	 *            the insert statement to execute
	 * 
	 * @return the ID of the new item inserted, or -1 on error
	 */
	protected int executeSqlReturnIntKey(DataSource ds, String sqlStmt)
			throws Exception {
		Connection conn = null;
		Statement stmt = null;
		boolean error = false;
		int key = -1;

		// Create Connection and execute SQL
		try {
			conn = getConnection(ds);
			stmt = conn.createStatement();
			stmt.execute(sqlStmt, Statement.RETURN_GENERATED_KEYS);

			// Retrieve the auto generated key(s).
			ResultSet rs = stmt.getGeneratedKeys();
			if (rs == null) {
				error = true;
				throw new SQLException("error getting generated key");
			}

			if (rs.next()) {
				key = rs.getInt(1);
			} else {
				logger.info("no resultset for getGeneratedKeys");
			}

			// Close statement and Connection
			stmt.close();

		} catch (SQLException sqlExcept) {
			error = true;
			throw sqlExcept;
		} catch (Exception except) {
			error = true;
			throw except;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				error = true;
				throw e;
			}
		}

		return (!error) ? key : -1;
	}

	/**
	 * Method gets a connection from the connection pool and then executes the
	 * string passed in as parameter.
	 * 
	 * @param ds
	 *            connected XA datasource
	 * @param sqlStmt
	 *            the update statement to execute
	 * 
	 * @return true if update was success, false otherwise
	 */
	protected boolean executeSqlReturnBoolean(DataSource ds, String sqlStmt)
			throws Exception {
		Connection conn = null;
		Statement stmt = null;
		boolean error = false;
		int updCount = -1;

		// Create Connection and execute SQL
		try {
			conn = getConnection(ds);
			stmt = conn.createStatement();
			stmt.execute(sqlStmt, Statement.RETURN_GENERATED_KEYS);

			// @Important check what the return type for executing a delete
			// statement is. I.e. how to verify it worked.
			// Check update counts - should be at least 1 (more if there were
			// cascading effects)
			updCount = stmt.getUpdateCount();
			// Close statement and Connection
			stmt.close();
		} catch (SQLException sqlExcept) {

			error = true;
			throw sqlExcept;
		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				error = true;
				throw e;
			}
		}

		// Default return false if execution was unsuccessful
		return (updCount >= 1 && !error) ? true : false;

	}

	protected void executeSql(DataSource ds, String sqlStmt) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		boolean error = false;

		// Create Connection and execute SQL
		try {
			conn = getConnection(ds);
			stmt = conn.createStatement();
			stmt.execute(sqlStmt);

			stmt.close();
		} catch (SQLException sqlExcept) {

			error = true;
			throw sqlExcept;
		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				error = true;
				throw e;
			}
		}

	}

	protected String executeStoredProcedureReturnString(DataSource ds,
			String callableSqlStmt, UUID inputParameter) throws Exception {
		Connection conn = null;
		CallableStatement stmt = null;
		boolean error = false;
		String result = "";

		// Create Connection and execute SQL
		try {
			conn = getConnection(ds);
			stmt = conn.prepareCall(callableSqlStmt);
			stmt.setString(1, inputParameter.toString());
			stmt.registerOutParameter(2, Types.VARCHAR);
			stmt.execute();
			result = stmt.getString(2);
			stmt.close();
		} catch (SQLException sqlExcept) {

			error = true;
			throw sqlExcept;
		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				error = true;
				throw e;
			}
		}

		return result;
	}

	/**
	 * execute an SQL select statement and return the retrieved ResultSet as XML
	 * String Method taken from
	 * http://www.developer.com/db/article.php/10920_3329001_1
	 * 
	 * @param sqlStmt
	 *            The sql statement to be executed
	 * @param ds
	 *            connected XA datasource
	 * @return The XML representation as string
	 */
	protected String executeSqlSelectReturnXMLString(String sqlStmt,
			DataSource ds) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		boolean error = false;
		String xmlString = null;
		// Create Connection and execute SQL
		try {
			conn = getConnection(ds);
			stmt = conn.createStatement();

			ResultSet rs = stmt.executeQuery(sqlStmt);

			// TODO - Check for empty resultset ... following doesn't work
			// because the cursor can't be reset
			// if (!rs.next())
			// return null;
			// rs.beforeFirst(); // Reset the resultSet cursor

			// ////// turn results set into DOM
			DocumentBuilderFactory factory = DocumentBuilderFactory
					.newInstance();
			DocumentBuilder builder = factory.newDocumentBuilder();
			Document returnDoc = builder.newDocument();

			// we need to supply a name space to apply with xpath
			// Element results = returnDoc.createElement("SILVERCOMPONENT");
			Element results = returnDoc.createElementNS("silver",
					"SILVERCOMPONENT");
			returnDoc.appendChild(results);
			ResultSetMetaData rsmd = rs.getMetaData();
			int colCount = rsmd.getColumnCount();
			while (rs.next()) {

				Element row = returnDoc.createElement(rsmd.getTableName(1));
				results.appendChild(row);

				for (int i = 1; i <= colCount; i++) {
					String columnName = rsmd.getColumnName(i);
					Object value = rs.getObject(i);
					Element node = returnDoc.createElement(columnName);
					if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR) // decode
						// strings
						node.appendChild(returnDoc.createTextNode(myBCodec
								.decode(value.toString())));
					else
						node.appendChild(returnDoc.createTextNode(value
								.toString()));
					row.appendChild(node);
				}
			}

			// ///////////////
			// Generate the XML

			// set up a transformer
			TransformerFactory transfac = TransformerFactory.newInstance();
			Transformer trans = transfac.newTransformer();
			trans.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
			trans.setOutputProperty(OutputKeys.INDENT, "yes");

			// create string from xml tree
			StringWriter sw = new StringWriter();
			StreamResult result = new StreamResult(sw);
			DOMSource source = new DOMSource(returnDoc);
			trans.transform(source, result);
			xmlString = sw.toString();

			// Close statement and Connection
			stmt.close();
		} catch (SQLException sqlExcept) {
			error = true;

			throw sqlExcept;
		} catch (Exception except) {
			error = true;

			throw except;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				error = true;
				throw e;
			}
		}
		// return the generated key value or null if error persists
		return (!error) ? xmlString : null;
	}

	/***
	 * Executes an sql statement passed in as parameter and returns the obtained
	 * result set as a ArrayList<Integer> - used for returning Ids of items in
	 * the DB
	 * 
	 * @param ds
	 *            connected XA datasource
	 * @param sqlStmt
	 *            The SQL statement
	 * 
	 * @return The list of Integer ids, with a list of size 0 if no items were
	 *         found.
	 */
	protected ArrayList<UUID> executeSqlReturnUUIDArrayList(DataSource ds,
			String sqlStmt) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		boolean error = false;

		ArrayList<UUID> ids = new ArrayList<UUID>();

		// Create Connection and execute SQL
		try {
			conn = getConnection(ds);
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlStmt);

			// Extract the ArrayList from the ResultSet
			if (rs != null) {
				try {
					while (rs.next()) {
						try {
							ids.add(UUID.fromString(rs.getString(1)));
						} catch (SQLException e) {
							error = true;
							throw e;
						}
					}
				} catch (SQLException e) {
					error = true;
					throw e;
				}
			} else
				ids = null;

			// Close statement.
			stmt.close();

		} catch (SQLException sqlExcept) {

			error = true;
			throw sqlExcept;
		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				throw e;
			}
		}

		return (!error) ? ids : null;
	}

	protected ArrayList<String> executeSqlReturnDecodedStringArrayList(
			DataSource ds, String sqlStmt) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		boolean error = false;

		ArrayList<String> results = new ArrayList<String>();

		// Create Connection and execute SQL
		try {
			conn = getConnection(ds);
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlStmt);

			// Extract the ArrayList from the ResultSet
			if (rs != null) {
				try {
					while (rs.next()) {
						try {
							results.add(myBCodec.decode(rs.getString(1)));
						} catch (SQLException e) {
							error = true;
							throw e;
						}
					}
				} catch (SQLException e) {
					error = true;
					throw e;
				}
			} else
				results = null;

			// Close statement.
			stmt.close();

		} catch (SQLException sqlExcept) {

			error = true;
			throw sqlExcept;
		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				throw e;
			}
		}

		return (!error) ? results : null;
	}

	/***
	 * Executes an sql statement passed in as parameter and returns the obtained
	 * result set as a ArrayList<Integer> - used for returning Ids of items in
	 * the DB
	 * 
	 * @param ds
	 *            connected XA datasource
	 * @param sqlStmt
	 *            The SQL statement
	 * 
	 * @return The list of Integer ids
	 */
	protected UUID executeMultipleOrSingleRowSqlReturnFirstUUID(DataSource ds,
			String sqlStmt) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		boolean error = false;
		UUID id = null;

		// Create Connection and execute SQL
		try {
			conn = getConnection(ds);
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlStmt);

			// Extract the ArrayList from the ResultSet
			if (rs != null) {
				if (rs.next())
					id = UUID.fromString(rs.getString(1));
			} else
				id = null;

			// Close statement.
			stmt.close();

		} catch (SQLException sqlExcept) {

			error = true;
			throw sqlExcept;
		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				throw e;
			}
		}

		return (!error) ? id : null;
	}

	/***
	 * Executes an sql statement passed in as parameter and returns the obtained
	 * result set as a HashMap<Integer, String> - used for returning Ids and the
	 * corresponding names of items in the DB
	 * 
	 * @param sqlStmt
	 *            The SQL statement to be executed
	 * @param ds
	 *            connected XA datasource
	 * @return A hashMap of Integer Id numbers and String names
	 */
	protected HashMap<Integer, String> executeSqlReturnIntegerStringHashMap(
			String sqlStmt, DataSource ds) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		HashMap<Integer, String> idNames = new HashMap<Integer, String>();
		boolean error = false;

		// Create Connection and execute SQL
		try {
			conn = getConnection(ds);
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlStmt);

			// Extract the ArrayList from the ResultSet
			if (rs != null) {
				try {
					while (rs.next()) {
						try {
							idNames.put(Integer.valueOf(rs.getInt(1)),
									myBCodec.decode(rs.getString(2)));
						} catch (SQLException e) {
							error = true;
							throw e;
						}
					}
				} catch (SQLException e) {
					error = true;
					throw e;
				}
			} else
				idNames = null;

			// Close statement and Connection
			stmt.close();

		} catch (SQLException sqlExcept) {

			error = true;
			throw sqlExcept;
		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				error = true;
				throw e;
			}
		}

		return (!error) ? idNames : null;

	}

	/***
	 * Executes an sql statement passed in as parameter and returns the obtained
	 * result set as a ArrayList<Integer> - used for returning Ids of items in
	 * the DB
	 * 
	 * @param ds
	 *            TODO
	 * @param sqlStmt
	 *            The SQL statement
	 * 
	 * @return The list of Integer ids
	 */
	protected ArrayList<Integer> executeSqlReturnIntegerArrayList(
			DataSource ds, String sqlStmt) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		boolean error = false;

		ArrayList<Integer> ids = new ArrayList<Integer>();

		// Create Connection and execute SQL
		try {
			conn = getConnection(ds);
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlStmt);

			// Extract the ArrayList from the ResultSet
			if (rs != null) {
				try {
					while (rs.next()) {
						try {
							ids.add(Integer.valueOf(rs.getInt(1)));
						} catch (SQLException e) {
							error = true;
							throw e;
						}
					}
				} catch (SQLException e) {
					error = true;
					throw e;
				}
			} else
				ids = null;

			// Close statement.
			stmt.close();

		} catch (SQLException sqlExcept) {

			error = true;
			throw sqlExcept;
		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				throw e;
			}
		}

		return (!error) ? ids : null;
	}

	/***
	 * Executes an sql statement passed in as parameter and returns the obtained
	 * result set as a HashMap<UUID, String> - used for returning Ids and the
	 * corresponding names of items in the DB
	 * 
	 * @param sqlStmt
	 *            The SQL statement to be executed
	 * @param ds
	 *            connected XA datasource
	 * @return A hashMap of Integer Id numbers and String names
	 */
	protected HashMap<UUID, String> executeSqlReturnUUIDStringHashMap(
			String sqlStmt, DataSource ds) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		HashMap<UUID, String> idNames = new HashMap<UUID, String>();
		boolean error = false;

		// Create Connection and execute SQL
		try {
			conn = getConnection(ds);
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlStmt);

			// Extract the ArrayList from the ResultSet
			if (rs != null) {
				try {
					while (rs.next()) {
						try {
							idNames.put(UUID.fromString(rs.getString(1)),
									myBCodec.decode(rs.getString(2)));
						} catch (SQLException e) {
							error = true;
							throw e;
						}
					}
				} catch (SQLException e) {
					error = true;
					throw e;
				}
			} else
				idNames = null;

			// Close statement and Connection
			stmt.close();

		} catch (SQLException sqlExcept) {

			error = true;
			throw sqlExcept;
		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				error = true;
				throw e;
			}
		}

		return (!error) ? idNames : null;

	}

	/**
	 * Similar to executeSqlReturnUUIDStringHashMap, this procedure executes an
	 * sql statement passed in as parameter and returns the obtained result set
	 * as a BiMap<UUID, String> - used for returning ids and correspoind
	 * contents of string
	 * 
	 * NOTE: this part of routine works but don't need it for now
	 * 
	 * @param sqlStmt
	 * @param ds
	 * @return
	 * @throws Exception
	 */
	// protected BiMap<UUID, String>
	// executeSqlReturnUUIDKeyColumnAndStringColumnInBiMap(
	// String sqlStmt, DataSource ds) throws Exception {
	// Connection conn = null;
	// Statement stmt = null;
	// BiMap<UUID, String> idNames = HashBiMap.create();
	// boolean error = false;
	//
	// // Create Connection and execute SQL
	// try {
	// conn = getConnection(ds);
	// stmt = conn.createStatement();
	// ResultSet rs = stmt.executeQuery(sqlStmt);
	//
	// // Extract the ArrayList from the ResultSet
	// if (rs != null) {
	// try {
	// while (rs.next()) {
	// try {
	// idNames.put(UUID.fromString(rs.getString(1)),
	// myBCodec.decode(rs.getString(2)));
	// } catch (SQLException e) {
	// error = true;
	// throw e;
	// }
	// }
	// } catch (SQLException e) {
	// error = true;
	// throw e;
	// }
	// } else
	// idNames = null;
	//
	// // Close statement and Connection
	// stmt.close();
	//
	// } catch (SQLException sqlExcept) {
	//
	// error = true;
	// throw sqlExcept;
	// } catch (Exception except) {
	//
	// error = true;
	// throw except;
	// } finally {
	// try {
	// closeConnection(ds, conn, error);
	// } catch (Exception e) {
	// error = true;
	// throw e;
	// }
	// }
	// return null;
	// }

	/***
	 * Executes an sql statement passed in as parameter and returns the obtained
	 * result set as a int
	 * 
	 * @param ds
	 *            connected XA datasource
	 * @param sqlStmt
	 *            The SQL statement
	 * 
	 * @return The integer value obtained from the query
	 */
	protected int executeSqlReturnInt(DataSource ds, String sqlStmt)
			throws Exception {
		Connection conn = null;
		Statement stmt = null;
		boolean error = false;
		int retVal = -1;

		// Create Connection and execute SQL
		try {
			conn = getConnection(ds);
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlStmt);

			// Extract the integer value from the result set
			if (rs != null) {
				try {
					// TODO - consider verifying that there's only a single row
					// in resultSet
					while (rs.next()) {
						try {
							retVal = rs.getInt(1);
						} catch (SQLException e) {
							error = true;
							throw e;
						}

					}
				} catch (SQLException e) {
					error = true;
					throw e;
				}
			} else
				retVal = -1;

			// Close statement and Connection
			stmt.close();

		} catch (SQLException sqlExcept) {

			error = true;
			throw sqlExcept;
		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				error = true;
				throw e;
			}
		}

		return (!error) ? retVal : -1;
	}

	/***
	 * Executes an sql statement passed in as parameter and returns the obtained
	 * result set as a decoded String
	 * 
	 * @param ds
	 *            connected XA datasource
	 * @param sqlStmt
	 *            The SQL statement
	 * 
	 * @return The String obtained from the query
	 */
	protected String executeSqlReturnString(DataSource ds, String sqlStmt)
			throws Exception {
		Connection conn = null;
		Statement stmt = null;
		String retVal = null;
		String decoded = null;
		boolean error = false;

		// Create Connection and execute SQL
		try {
			conn = getConnection(ds);
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlStmt);

			// Extract the String value from the result set
			if (rs != null) {
				try {
					// TODO - consider checking to make sure there's only a
					// single row in rs
					while (rs.next()) {
						try {
							retVal = rs.getString(1);
						} catch (SQLException e) {
							error = true;
							throw e;
						}
					}
					// DECODE THE STRING
					decoded = myBCodec.decode(retVal);
				} catch (SQLException e) {
					error = true;
					throw e;
				}
			}

			// Close statement and Connection
			stmt.close();
		} catch (SQLException sqlExcept) {

			error = true;
			throw sqlExcept;
		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				throw e;
			}
		}

		return (!error) ? decoded : null;
	}

	/***
	 * Executes an sql statement passed in as parameter and returns the first
	 * obtained result set as a decoded String
	 * 
	 * @param ds
	 *            connected XA datasource
	 * @param sqlStmt
	 *            The SQL statement
	 * 
	 * @return The String obtained from the query
	 */
	protected String executeMultipleOrSingleSqlReturnFirstString(DataSource ds,
			String sqlStmt) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		String retVal = null;
		String decoded = null;
		boolean error = false;

		// Create Connection and execute SQL
		try {
			conn = getConnection(ds);
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlStmt);

			// Extract the String value from the result set
			if (rs != null) {
				try {
					if (rs.next()) {
						try {
							retVal = rs.getString(1);
						} catch (SQLException e) {
							error = true;
							throw e;
						}
					}
					// DECODE THE STRING
					decoded = myBCodec.decode(retVal);
				} catch (SQLException e) {
					error = true;
					throw e;
				}
			}

			// Close statement and Connection
			stmt.close();
		} catch (SQLException sqlExcept) {

			error = true;
			throw sqlExcept;
		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				throw e;
			}
		}

		return (!error) ? decoded : null;
	}

	/***
	 * Executes an sql statement passed in as parameter and returns the obtained
	 * result set as a undecoded String
	 * 
	 * @param ds
	 *            connected XA datasource
	 * @param sqlStmt
	 *            The SQL statement
	 * 
	 * @return The String obtained from the query
	 */
	protected String executeSqlReturnUnEncodedString(DataSource ds,
			String sqlStmt) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		String retVal = null;

		boolean error = false;

		// Create Connection and execute SQL
		try {
			conn = getConnection(ds);
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlStmt);

			// Extract the String value from the result set
			if (rs != null) {
				try {
					// TODO - consider checking to make sure there's only a
					// single row in rs
					while (rs.next()) {
						try {
							retVal = rs.getString(1);
						} catch (SQLException e) {
							error = true;
							throw e;
						}
					}
				} catch (SQLException e) {
					error = true;
					throw e;
				}
			}

			// Close statement and Connection
			stmt.close();
		} catch (SQLException sqlExcept) {

			error = true;
			throw sqlExcept;
		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				throw e;
			}
		}

		return (!error) ? retVal : null;
	}

	/***
	 * Executes an sql statement passed in as parameter and returns the obtained
	 * ID key as a UUID
	 * 
	 * @param ds
	 *            specified datasource
	 * @param sqlStmt
	 *            The SQL statement
	 */
	protected void executeSqlReturnUUIDKey(DataSource ds, String sqlStmt)
			throws Exception {
		Connection conn = null;
		Statement stmt = null;
		int updCount = -1;

		boolean error = false;

		// Create Connection and execute SQL
		try {
			conn = getConnection(ds);
			stmt = conn.createStatement();
			stmt.executeUpdate(sqlStmt);
			updCount = stmt.getUpdateCount();
			// Close statement and Connection
			if (updCount < 1)
				throw new Exception();
			stmt.close();
		} catch (SQLException sqlExcept) {

			error = true;
			throw sqlExcept;
		} catch (Exception except) {
			error = true;
			throw except;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				throw e;
			}
		}
	}

	/**
	 * Executes an sql statement passed in as parameter and returns the obtained
	 * resultset as a UUID
	 * 
	 * @param ds
	 *            specified datasource
	 * @param sqlStmt
	 *            The SQL statement
	 * @return UUID object
	 */
	protected UUID executeSqlReturnUUID(DataSource ds, String sqlStmt)
			throws Exception {
		Connection conn = null;
		Statement stmt = null;
		String retVal = null;
		UUID result = null;
		boolean error = false;

		// Create Connection and execute SQL
		try {
			conn = getConnection(ds);
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlStmt);

			// Extract the String value from the result set
			if (rs != null) {
				try {
					// TODO - consider checking to make sure there's only a
					// single row in rs
					while (rs.next()) {
						try {
							retVal = rs.getString(1);
						} catch (SQLException e) {
							error = true;
							throw e;
						}
					}
					// DECODE THE STRING
					if (retVal != null)
						result = UUID.fromString(retVal);
				} catch (SQLException e) {
					error = true;
					throw e;
				}
			}

			// Close statement and Connection
			stmt.close();
		} catch (SQLException sqlExcept) {

			error = true;
			throw sqlExcept;
		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				throw e;
			}
		}

		return (!error) ? result : null;
	}

	/***
	 * Executes an sql statement passed in as parameter and returns the obtained
	 * result set as a Date
	 * 
	 * @param ds
	 *            connected XA datasource
	 * @param sqlStmt
	 *            The SQL statement
	 * 
	 * @return The Date obtained from the query
	 */
	protected Date executeSqlReturnDate(DataSource ds, String sqlStmt)
			throws Exception {
		Connection conn = null;
		Statement stmt = null;
		Date retVal = null;
		boolean error = false;

		// Create Connection and execute SQL
		try {
			conn = getConnection(ds);
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlStmt);

			// Extract the Date value from the result set
			if (rs != null) {
				try {
					// TODO - consider checking to make sure there's only a
					// single row in rs
					while (rs.next()) {
						try {
							retVal = rs.getDate(1);
						} catch (SQLException e) {
							error = true;
							throw e;
						}

					}
				} catch (SQLException e) {
					error = true;
					throw e;
				}
			} else
				retVal = null; // only get here by error

			// Close statement and Connection
			stmt.close();

		} catch (SQLException sqlExcept) {

			error = true;
			throw sqlExcept;
		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				error = true;
				throw e;
			}
		}
		return (!error) ? retVal : null;
	}

	/***
	 * Executes an sql statement passed in as parameter and returns the obtained
	 * result set as a Date stating ONLY time
	 * 
	 * @param ds
	 *            connected XA datasource
	 * @param sqlStmt
	 *            The SQL statement
	 * 
	 * @return The Date obtained from the query
	 */
	protected Date executeSqlReturnTime(DataSource ds, String sqlStmt)
			throws Exception {
		Connection conn = null;
		Statement stmt = null;
		Date retVal = null;
		boolean error = false;

		// Create Connection and execute SQL
		try {
			conn = getConnection(ds);
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlStmt);

			// Extract the Date value from the result set
			if (rs != null) {
				try {
					// TODO - consider checking to make sure there's only a
					// single row in rs
					while (rs.next()) {
						try {
							// Only retrieve time
							retVal = rs.getTimestamp(1);
						} catch (SQLException e) {
							error = true;
							throw e;
						}

					}
				} catch (SQLException e) {
					error = true;
					throw e;
				}
			} else
				retVal = null; // only get here by error

			// Close statement and Connection
			stmt.close();

		} catch (SQLException sqlExcept) {

			error = true;
			throw sqlExcept;
		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				throw e;
			}
		}

		return retVal;

	}

	/***
	 * Executes an sp statement passed in as parameter and returns the obtained
	 * result set as a int
	 * 
	 * @param ds
	 *            connected XA datasource
	 * @param sqlStmt
	 *            The SP statement
	 * 
	 * @return The integer value obtained from the query
	 */
	protected int executeStoredProcedureReturnInt(DataSource ds,
			String sqlStmt, int retPos) throws Exception {

		Connection conn = null;
		CallableStatement cstmt = null;
		boolean error = false;
		int retVal = -1;

		// Create Connection and execute SP

		try {
			conn = getConnection(ds);
			cstmt = conn.prepareCall(sqlStmt);
			cstmt.registerOutParameter(retPos, java.sql.Types.INTEGER);
			retVal = cstmt.getInt(retPos);
			cstmt.execute();
			// Close statement
			cstmt.close();

		} catch (SQLException sqlExcept) {

			error = true;
			throw sqlExcept;
		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				throw e;
			}
		}

		return (!error) ? retVal : -1;
	}

	protected String[] executeSqlReturnUserStringArray(int arguments,
			DataSource ds, String sqlStmt) throws Exception {
		Connection conn = null;
		Statement stmt = null;
		String[] retVal = new String[arguments];
		boolean error = false;

		// Create Connection and execute SQL
		try {
			conn = getConnection(ds);
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sqlStmt);

			// Extract the String value from the result set
			if (rs != null) {
				try {
					// TODO - consider checking to make sure there's only a
					// single row in rs
					while (rs.next()) {
						try {
							// store Identity
							retVal[0] = rs.getString(1);
							// next, store base 64 encoded strings
							for (int i = 1; i < arguments; i++) {
								retVal[i] = myBCodec
										.decode(rs.getString(i + 1));
							}
						} catch (SQLException e) {
							error = true;
							throw e;
						}
					}
					// DECODE THE STRING

				} catch (SQLException e) {
					error = true;
					throw e;
				}
			}

			// Close statement and Connection
			stmt.close();
		} catch (SQLException sqlExcept) {

			error = true;
			throw sqlExcept;
		} catch (Exception except) {

			error = true;
			throw except;
		} finally {
			try {
				closeConnection(ds, conn, error);
			} catch (Exception e) {
				throw e;
			}
		}

		return (!error) ? retVal : null;
	}

	protected String getRandomUUID() {
		return UUID.randomUUID().toString();
	}

	// ping
	protected final boolean pingConnection(DataSource ds) throws SQLException {
		boolean result = false;
		Connection a = null;
		try {
			a = ds.getConnection();
			result = a.isValid(3);
		} catch (SQLException e) {
			throw e;
		} finally {
			if (a != null)
				a.close();
		}
		return result;
	}

}
